﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace DoeObjects
{
    public class UserCategory
    {
        public int CategoryId { get; set; }
        public string Name { get; set; }
        public int LoanPeriod { get; set; }

        public static UserCategory getById(int id)
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand("SELECT * FROM [UserCategory] WHERE [UserCategory].UserCategoryId = @id", connection);
            command.Parameters.AddWithValue("@id", id);

            UserCategory cat = null;
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    cat = getCurrentUserCategory(reader);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return cat;
        }

        public static UserCategory getByName(string name)
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
			SqlCommand command = new SqlCommand("SELECT * FROM [UserCategory] WHERE [UserCategory].UserCategoryName = @name", connection);
            SqlParameter nameParameter = new SqlParameter("@name", SqlDbType.VarChar);
            nameParameter.Value = name;
            command.Parameters.Add(nameParameter);

            UserCategory cat = null;
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    cat = getCurrentUserCategory(reader);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return cat;
        }

        public static List<UserCategory> getAll()
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
			SqlCommand command = new SqlCommand("SELECT * FROM [UserCategory]", connection);

            List<UserCategory> result = new List<UserCategory>();
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    result.Add(getCurrentUserCategory(reader));
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return result;
        }

        internal static UserCategory getCurrentUserCategory(SqlDataReader reader)
        {
            UserCategory cat = null;
            if (reader != null)
            {
                cat = new UserCategory();
                cat.CategoryId = (int)reader["UserCategoryId"];
                cat.Name = reader["UserCategoryName"] as string;
                cat.LoanPeriod = (int)reader["LoanPeriod"];
            }

            return cat;
        }
    }
}
